package saleMarket.dao;

import lombok.extern.slf4j.Slf4j;
import saleMarket.domain.Shop;
import saleMarket.domain.ShopOwner;
import saleMarket.service.ShopOwnerService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;

@Slf4j
public class ShopDao {
    private static ShopDao shopDao = new ShopDao();
    private ShopDao() {}
    public static ShopDao getInstance() {
        // 静态变量ShopDao为空时，才创建ShopDao对象。
        // 第二次调用本方法ShopDao就不空了，不再创建对象。
        if (shopDao == null) {
            shopDao = new ShopDao();
        }
        return shopDao;
    }

    public Collection<Shop> findAll(Connection connection) throws SQLException {
        Collection<Shop> shops = new ArrayList<>();
        StringBuilder sql_select = new StringBuilder("SELECT*FROM shop");
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            int shopowner_id = resultSet_select.getInt("shopowner_id");
            String areaCode = resultSet_select.getString("areaCode");
            String no = resultSet_select.getString("no");
            ShopOwner shopOwner = ShopOwnerService.getInstance().find(shopowner_id);
            Shop shop = new Shop(id,areaCode,shopOwner,no);
            shops.add(shop);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return shops;
    }

    public Shop find(Integer id,Connection connection) throws SQLException {
        String shop_sql = "select * from shop where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(shop_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找shop
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建Shop对象
        Shop shop = new Shop(
                id,resultSet.getString("areaCode"),
                ShopOwnerService.getInstance().find(resultSet.getInt("shopowner_id")),
                resultSet.getString("no"));
        //关闭
        JdbcHelper.close(resultSet,pstmt);
        return shop;
    }

    public boolean update(Shop shop,Connection conn) throws SQLException {
        //编辑预编译语句
        PreparedStatement update_pstmt = conn.prepareStatement(
                "UPDATE shop SET areaCode=?,shopowner_id = ? ,no = ? WHERE id = ?");
        //为预编译参数赋值
        update_pstmt.setString(1, shop.getAreaCode());
        update_pstmt.setInt(2, shop.getShopowner().getId());
        update_pstmt.setString(3, shop.getNo());
        update_pstmt.setInt(4, shop.getId());
        //获取执行结果
        int affectedRow = update_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(update_pstmt);
        //返回执行结果
        return affectedRow>0;
    }

    public boolean add(Shop shop,Connection connection) throws SQLException {

        String addShop_sql = "INSERT INTO shop(areaCode,shopowner_id,no)" +
                " VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addShop_sql);
        //为预编译参数赋值
        pstmt.setString(1, shop.getAreaCode());
        pstmt.setInt(2, 5);
        pstmt.setString(3, shop.getNo());
        //添加Shop 并判断是否添加成功
        //获取执行结果
        int affectedRow = pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(pstmt);
        return affectedRow>0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {

        String deleteShop_sql = "delete from shop where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteShop_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //获取执行结果
        int affectedRow = pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(pstmt);
        //根据id删除Shop 并判断是否删除成功
        return affectedRow>0;
    }
}
